"""MySQL数据库仓库层"""

from typing import List, Dict, Any, Optional
from sqlalchemy.orm import Session
from sqlalchemy import text
from datetime import datetime
import logging
import uuid

logger = logging.getLogger(__name__)


class MySQLRepository:
    """MySQL数据库操作仓库"""
    
    def __init__(self, db: Session):
        self.db = db
    
    # ==================== 用户操作 ====================
    
    async def create_user(self, user_data: Dict[str, Any]) -> Dict[str, Any]:
        """创建用户"""
        try:
            user_id = user_data.get("user_id", str(uuid.uuid4()))
            
            query = text("""
                INSERT INTO users (user_id, username, email, password_hash, is_active, created_at, updated_at, uuid)
                VALUES (:user_id, :username, :email, :password_hash, :is_active, :created_at, :updated_at, :uuid)
            """)
            
            self.db.execute(query, {
                "user_id": user_id,
                "username": user_data.get("username"),
                "email": user_data.get("email"),
                "password_hash": user_data.get("password_hash"),
                "is_active": user_data.get("is_active", "true"),
                "created_at": datetime.now(),
                "updated_at": datetime.now(),
                "uuid": str(uuid.uuid4())
            })
            
            self.db.commit()
            return {"user_id": user_id, "success": True}
            
        except Exception as e:
            self.db.rollback()
            logger.error(f"创建用户失败: {e}")
            return {"success": False, "error": str(e)}
    
    async def get_user_by_email(self, email: str) -> Optional[Dict[str, Any]]:
        """根据邮箱获取用户"""
        try:
            query = text("SELECT * FROM users WHERE email = :email")
            result = self.db.execute(query, {"email": email}).fetchone()
            
            if result:
                return dict(result._mapping)
            return None
            
        except Exception as e:
            logger.error(f"获取用户失败: {e}")
            return None
    
    async def get_user_by_id(self, user_id: str) -> Optional[Dict[str, Any]]:
        """根据ID获取用户"""
        try:
            query = text("SELECT * FROM users WHERE user_id = :user_id")
            result = self.db.execute(query, {"user_id": user_id}).fetchone()
            
            if result:
                return dict(result._mapping)
            return None
            
        except Exception as e:
            logger.error(f"获取用户失败: {e}")
            return None
    
    async def update_user_login(self, user_id: str) -> bool:
        """更新用户登录时间"""
        try:
            query = text("""
                UPDATE users 
                SET last_login_at = :last_login_at, updated_at = :updated_at
                WHERE user_id = :user_id
            """)
            
            self.db.execute(query, {
                "user_id": user_id,
                "last_login_at": datetime.now(),
                "updated_at": datetime.now()
            })
            
            self.db.commit()
            return True
            
        except Exception as e:
            self.db.rollback()
            logger.error(f"更新用户登录时间失败: {e}")
            return False
    
    async def update_user_emotion(self, user_id: str, emotion: str, emotion_history: List[Dict]) -> bool:
        """更新用户情绪状态"""
        try:
            import json
            
            query = text("""
                UPDATE users 
                SET current_emotion = :emotion, emotion_history = :emotion_history, 
                    emotion_updated_at = :updated_at, updated_at = :updated_at
                WHERE user_id = :user_id
            """)
            
            self.db.execute(query, {
                "user_id": user_id,
                "emotion": emotion,
                "emotion_history": json.dumps(emotion_history),
                "updated_at": datetime.now()
            })
            
            self.db.commit()
            return True
            
        except Exception as e:
            self.db.rollback()
            logger.error(f"更新用户情绪失败: {e}")
            return False
    
    # ==================== 对话操作 ====================
    
    async def create_conversation(self, conversation_data: Dict[str, Any]) -> Dict[str, Any]:
        """创建对话"""
        try:
            conversation_id = conversation_data.get("conversation_id", str(uuid.uuid4()))
            
            query = text("""
                INSERT INTO conversations (conversation_id, user_id, title, message_count, is_active, 
                                         last_message_at, created_at, updated_at, uuid)
                VALUES (:conversation_id, :user_id, :title, :message_count, :is_active, 
                        :last_message_at, :created_at, :updated_at, :uuid)
            """)
            
            self.db.execute(query, {
                "conversation_id": conversation_id,
                "user_id": conversation_data.get("user_id"),
                "title": conversation_data.get("title", "新对话"),
                "message_count": conversation_data.get("message_count", 0),
                "is_active": conversation_data.get("is_active", "true"),
                "last_message_at": conversation_data.get("last_message_at"),
                "created_at": datetime.now(),
                "updated_at": datetime.now(),
                "uuid": str(uuid.uuid4())
            })
            
            self.db.commit()
            return {"conversation_id": conversation_id, "success": True}
            
        except Exception as e:
            self.db.rollback()
            logger.error(f"创建对话失败: {e}")
            return {"success": False, "error": str(e)}
    
    async def update_conversation(self, conversation_data: Dict[str, Any]) -> bool:
        """更新或创建对话"""
        try:
            conversation_id = conversation_data.get("conversation_id")
            
            # 检查对话是否存在
            check_query = text("SELECT conversation_id FROM conversations WHERE conversation_id = :conversation_id")
            existing = self.db.execute(check_query, {"conversation_id": conversation_id}).fetchone()
            
            if existing:
                # 更新现有对话
                query = text("""
                    UPDATE conversations 
                    SET title = :title, message_count = :message_count, 
                        last_message_at = :last_message_at, updated_at = :updated_at
                    WHERE conversation_id = :conversation_id
                """)
                
                self.db.execute(query, {
                    "conversation_id": conversation_id,
                    "title": conversation_data.get("title"),
                    "message_count": conversation_data.get("message_count"),
                    "last_message_at": conversation_data.get("last_message_at"),
                    "updated_at": datetime.now()
                })
            else:
                # 创建新对话
                await self.create_conversation(conversation_data)
            
            self.db.commit()
            return True
            
        except Exception as e:
            self.db.rollback()
            logger.error(f"更新对话失败: {e}")
            return False
    
    async def get_user_conversations(self, user_id: str, limit: int = 20) -> List[Dict[str, Any]]:
        """获取用户对话列表"""
        try:
            query = text("""
                SELECT * FROM conversations 
                WHERE user_id = :user_id AND is_active = 'true'
                ORDER BY last_message_at DESC, created_at DESC
                LIMIT :limit
            """)
            
            results = self.db.execute(query, {"user_id": user_id, "limit": limit}).fetchall()
            
            return [dict(row._mapping) for row in results]
            
        except Exception as e:
            logger.error(f"获取用户对话列表失败: {e}")
            return []
    
    async def get_conversation(self, conversation_id: str) -> Optional[Dict[str, Any]]:
        """获取单个对话"""
        try:
            query = text("SELECT * FROM conversations WHERE conversation_id = :conversation_id")
            result = self.db.execute(query, {"conversation_id": conversation_id}).fetchone()
            
            if result:
                return dict(result._mapping)
            return None
            
        except Exception as e:
            logger.error(f"获取对话失败: {e}")
            return None
    
    async def delete_conversation(self, conversation_id: str) -> bool:
        """删除对话"""
        try:
            # 软删除，设置为非活跃状态
            query = text("""
                UPDATE conversations 
                SET is_active = 'false', updated_at = :updated_at
                WHERE conversation_id = :conversation_id
            """)
            
            self.db.execute(query, {
                "conversation_id": conversation_id,
                "updated_at": datetime.now()
            })
            
            self.db.commit()
            return True
            
        except Exception as e:
            self.db.rollback()
            logger.error(f"删除对话失败: {e}")
            return False
    
    # ==================== 消息操作 ====================
    
    async def save_message(self, message_data: Dict[str, Any]) -> Dict[str, Any]:
        """保存消息"""
        try:
            message_id = message_data.get("message_id", str(uuid.uuid4()))
            
            query = text("""
                INSERT INTO messages (message_id, conversation_id, role, content, model_name, 
                                    temperature, message_metadata, feedback, created_at, updated_at, uuid)
                VALUES (:message_id, :conversation_id, :role, :content, :model_name, 
                        :temperature, :message_metadata, :feedback, :created_at, :updated_at, :uuid)
            """)
            
            self.db.execute(query, {
                "message_id": message_id,
                "conversation_id": message_data.get("conversation_id"),
                "role": message_data.get("role"),
                "content": message_data.get("content"),
                "model_name": message_data.get("model_name"),
                "temperature": message_data.get("temperature"),
                "message_metadata": message_data.get("message_metadata"),
                "feedback": message_data.get("feedback", 0),
                "created_at": datetime.now(),
                "updated_at": datetime.now(),
                "uuid": str(uuid.uuid4())
            })
            
            self.db.commit()
            return {"message_id": message_id, "success": True}
            
        except Exception as e:
            self.db.rollback()
            logger.error(f"保存消息失败: {e}")
            return {"success": False, "error": str(e)}
    
    async def get_conversation_messages(self, conversation_id: str, limit: int = 100) -> List[Dict[str, Any]]:
        """获取对话消息"""
        try:
            query = text("""
                SELECT * FROM messages 
                WHERE conversation_id = :conversation_id
                ORDER BY created_at ASC
                LIMIT :limit
            """)
            
            results = self.db.execute(query, {"conversation_id": conversation_id, "limit": limit}).fetchall()
            
            return [dict(row._mapping) for row in results]
            
        except Exception as e:
            logger.error(f"获取对话消息失败: {e}")
            return []
    
    async def get_message_count(self, conversation_id: str) -> int:
        """获取对话消息数量"""
        try:
            query = text("SELECT COUNT(*) as count FROM messages WHERE conversation_id = :conversation_id")
            result = self.db.execute(query, {"conversation_id": conversation_id}).fetchone()
            
            return result.count if result else 0
            
        except Exception as e:
            logger.error(f"获取消息数量失败: {e}")
            return 0
    
    async def update_message_feedback(self, message_id: str, feedback: int) -> bool:
        """更新消息反馈"""
        try:
            query = text("""
                UPDATE messages 
                SET feedback = :feedback, updated_at = :updated_at
                WHERE message_id = :message_id
            """)
            
            self.db.execute(query, {
                "message_id": message_id,
                "feedback": feedback,
                "updated_at": datetime.now()
            })
            
            self.db.commit()
            return True
            
        except Exception as e:
            self.db.rollback()
            logger.error(f"更新消息反馈失败: {e}")
            return False
    
    async def get_message_by_id(self, message_id: str) -> Optional[Dict[str, Any]]:
        """根据ID获取消息"""
        try:
            query = text("SELECT * FROM messages WHERE message_id = :message_id")
            result = self.db.execute(query, {"message_id": message_id}).fetchone()
            
            if result:
                return dict(result._mapping)
            return None
            
        except Exception as e:
            logger.error(f"获取消息失败: {e}")
            return None
    
    # ==================== 统计和分析 ====================
    
    async def get_user_statistics(self, user_id: str) -> Dict[str, Any]:
        """获取用户统计信息"""
        try:
            # 获取对话数量
            conv_query = text("SELECT COUNT(*) as count FROM conversations WHERE user_id = :user_id AND is_active = 'true'")
            conv_result = self.db.execute(conv_query, {"user_id": user_id}).fetchone()
            
            # 获取消息数量
            msg_query = text("""
                SELECT COUNT(*) as count FROM messages m
                JOIN conversations c ON m.conversation_id = c.conversation_id
                WHERE c.user_id = :user_id
            """)
            msg_result = self.db.execute(msg_query, {"user_id": user_id}).fetchone()
            
            # 获取最近活跃时间
            activity_query = text("""
                SELECT MAX(m.created_at) as last_activity FROM messages m
                JOIN conversations c ON m.conversation_id = c.conversation_id
                WHERE c.user_id = :user_id
            """)
            activity_result = self.db.execute(activity_query, {"user_id": user_id}).fetchone()
            
            return {
                "total_conversations": conv_result.count if conv_result else 0,
                "total_messages": msg_result.count if msg_result else 0,
                "last_activity": activity_result.last_activity if activity_result else None
            }
            
        except Exception as e:
            logger.error(f"获取用户统计失败: {e}")
            return {"total_conversations": 0, "total_messages": 0, "last_activity": None}
    
    async def get_system_statistics(self) -> Dict[str, Any]:
        """获取系统统计信息"""
        try:
            # 总用户数
            user_query = text("SELECT COUNT(*) as count FROM users WHERE is_active = 'true'")
            user_result = self.db.execute(user_query).fetchone()
            
            # 总对话数
            conv_query = text("SELECT COUNT(*) as count FROM conversations WHERE is_active = 'true'")
            conv_result = self.db.execute(conv_query).fetchone()
            
            # 总消息数
            msg_query = text("SELECT COUNT(*) as count FROM messages")
            msg_result = self.db.execute(msg_query).fetchone()
            
            # 今日活跃用户
            today_query = text("""
                SELECT COUNT(DISTINCT c.user_id) as count FROM conversations c
                JOIN messages m ON c.conversation_id = m.conversation_id
                WHERE DATE(m.created_at) = CURDATE()
            """)
            today_result = self.db.execute(today_query).fetchone()
            
            return {
                "total_users": user_result.count if user_result else 0,
                "total_conversations": conv_result.count if conv_result else 0,
                "total_messages": msg_result.count if msg_result else 0,
                "active_users_today": today_result.count if today_result else 0
            }
            
        except Exception as e:
            logger.error(f"获取系统统计失败: {e}")
            return {"total_users": 0, "total_conversations": 0, "total_messages": 0, "active_users_today": 0}
